package org.jeecg.modules.htxincailiao.account.mapper;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.poi.ss.formula.functions.T;
import org.jeecg.modules.htxincailiao.account.domain.vo.HTUserInfoVO;
import org.jeecg.modules.htxincailiao.account.domain.vo.HTUserStatisticsVO;
import org.jeecg.modules.htxincailiao.account.entity.HTPlatformUser;

import java.util.List;


public interface HTAccountMapper extends BaseMapper<HTPlatformUser> {
  final static String MULTIPLE_FACTOR_SELECT_FIELD = "(SELECT id, account_type, username, nickname, phone_number," +
          "password, " +
          "email, avatar, salt, audit_status, is_locked, tencent_account_id FROM ";
  final static String MULTIPLE_FACTOR_CONDITIONS = " WHERE username=#{username} Or phone_number=#{phoneNumber} OR email=#{email})";

  final static String CERTIFICATE_AUDIT_SELECT_FIELD = "(SELECT id, account_type, username, phone_number,password, " +
          "certificate, email, avatar, salt, audit_status, is_locked, tencent_account_id FROM ";



  final static String USERNAME_CONDITIONS = " WHERE username=#{username})";
  final static String PHONE_NUMBER_CONDITIONS = " WHERE phone_number=#{phoneNumber})";
  final static String EMAIL_CONDITIONS = " WHERE email=#{email})";
  final static String USER_ID_CONDITIONS = " WHERE id=#{id})";
  final static String CERTIFICATE_AUDIT_STATUS_CONDITIONS = " WHERE certificate_audit_status=#{certificateAuditStatus})";

  @Select("<script>" +
          "select * from (" + MULTIPLE_FACTOR_SELECT_FIELD + "ht_guest"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_member"+  MULTIPLE_FACTOR_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_forums_expert"+  MULTIPLE_FACTOR_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_merchant"+  MULTIPLE_FACTOR_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_evaluation_agency"+  MULTIPLE_FACTOR_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_member"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_council"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_expert"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_secretary"+  MULTIPLE_FACTOR_CONDITIONS + ") t limit 1" +
          "</script>")
  HTPlatformUser queryFrontendUserByMultipleFactor(@Param("username") String username, @Param("phoneNumber") String phoneNumber, @Param("email") String email);



  @Select("<script>" +
          "select * from (" + MULTIPLE_FACTOR_SELECT_FIELD + "ht_guest"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_member"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_forums_expert"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_merchant"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_evaluation_agency"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_member"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_council"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_expert"+  USERNAME_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_secretary"+  USERNAME_CONDITIONS + ") t limit 1" +
          "</script>")
  HTPlatformUser queryFrontendUserByUsername(@Param("username") String username);



  @Select("<script>" +
          "select * from (" + MULTIPLE_FACTOR_SELECT_FIELD + "ht_member"+  PHONE_NUMBER_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_forums_expert"+  PHONE_NUMBER_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_merchant"+  PHONE_NUMBER_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_evaluation_agency"+  PHONE_NUMBER_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_member"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_council"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_expert"+  PHONE_NUMBER_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_secretary"+  PHONE_NUMBER_CONDITIONS + ") t limit 1" +
          "</script>")
  HTPlatformUser queryFrontendUserByPhoneNumber(@Param("phoneNumber") String phoneNumber);

  @Select("<script>" +
          "select * from (" + MULTIPLE_FACTOR_SELECT_FIELD + "ht_member"+  EMAIL_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_forums_expert"+  EMAIL_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_merchant"+  EMAIL_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_evaluation_agency"+  EMAIL_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance"+  EMAIL_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_member"+  EMAIL_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_council"+  EMAIL_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_expert"+  EMAIL_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_secretary"+  EMAIL_CONDITIONS + ") t limit 1" +
          "</script>")
  HTPlatformUser queryFrontendUserByEmail(@Param("email") String email);

  @Select("<script>" +
          "select * from (" + MULTIPLE_FACTOR_SELECT_FIELD + "ht_guest"+  USER_ID_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_member"+  USER_ID_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_forums_expert"+  USER_ID_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_merchant"+  USER_ID_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_evaluation_agency"+  USER_ID_CONDITIONS  + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance"+  USER_ID_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_member"+  USER_ID_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_council"+  USER_ID_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_expert"+  USER_ID_CONDITIONS + "UNION"+
          MULTIPLE_FACTOR_SELECT_FIELD + "ht_alliance_secretary"+  USER_ID_CONDITIONS + ") t limit 1" +
          "</script>")
  HTPlatformUser queryUserById(@Param("id") String id);

  @Select("<script>" +
          "select * from (" + CERTIFICATE_AUDIT_SELECT_FIELD + "ht_forums_expert"+  CERTIFICATE_AUDIT_STATUS_CONDITIONS  + "UNION"+
          CERTIFICATE_AUDIT_SELECT_FIELD + "ht_merchant"+  CERTIFICATE_AUDIT_STATUS_CONDITIONS  + "UNION"+
          CERTIFICATE_AUDIT_SELECT_FIELD + "ht_evaluation_agency"+  CERTIFICATE_AUDIT_STATUS_CONDITIONS  + "UNION"+
          CERTIFICATE_AUDIT_SELECT_FIELD + "ht_alliance"+  CERTIFICATE_AUDIT_STATUS_CONDITIONS + "UNION"+
          CERTIFICATE_AUDIT_SELECT_FIELD + "ht_alliance_member"+  CERTIFICATE_AUDIT_STATUS_CONDITIONS + ") t " +
          "</script>")
  List<HTPlatformUser> queryUserListByCertificateAuditStatus(Page<HTPlatformUser> page,
                                                             @Param("certificateAuditStatus")   Integer certificateAuditStatus);

  @Select("<script>" +
          " SELECT  " +
          " (SELECT COUNT(*) FROM ht_member) as member_count, " +
          " (SELECT COUNT(*) FROM ht_forums_expert) as forums_expert_count, " +
          " (SELECT COUNT(*) FROM ht_merchant) as merchant_count," +
          " (SELECT COUNT(*) FROM ht_evaluation_agency) as evaluation_agency_count," +
          " (SELECT COUNT(*) FROM ht_alliance) as alliance_count," +
          " (SELECT COUNT(*) FROM ht_alliance_member) as alliance_member_count," +
          " (SELECT T.member_count + T.forums_expert_count + T.merchant_count + T.evaluation_agency_count + T.alliance_count + T.alliance_member_count " +
          "FROM (SELECT  " +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_member) as member_count , " +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_forums_expert) as forums_expert_count, " +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_merchant) as merchant_count," +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_evaluation_agency) as evaluation_agency_count," +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_alliance) as alliance_count," +
          " (SELECT COUNT(IF(DATE_FORMAT(create_time,'%Y-%m-%d') = #{dayTime},id,NULL)) FROM ht_alliance_member) as" +
          " alliance_member_count ) as T)as day_total" +
          "</script>")
  HTUserStatisticsVO queryStatistics(@Param("dayTime")  String dayTime);



}
